﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLDBHYT.VO;
using System.Data;
using System.Data.SqlClient;


namespace QLDBHYT.DAO
{
    public class PhieuNhapDAO
    {
        private dbConnection conn;
        public PhieuNhapDAO()
        {
            conn = new dbConnection();
        }
        public DataTable searchAll(int thang, int nam)
        {
            string query = string.Format("select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,"
           + " isnull(c.sldauky,0) as sldauky,isnull(c.slnhap,0) as slnhap,isnull(sum(slxuat),0) as slxuat,isnull(slxuatdc,0) as slxuatdc,"
           + " isnull(c.sldauky,0) + isnull(c.slnhap,0) - isnull(sum(slxuat),0) - isnull(slxuatdc,0) as slcuoiky"
           + " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1"
           + " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu"
           + " and c.thang = @thang and c.nam = @nam"
           + " left join NT_PHIEUXUAT d on a.stt_nh = d.stt_nh and a.ma_hieu = d.ma_hieu"
           + " and d.thang = @thang and d.nam = @nam"
           + " where  a.chon = 1 and b.gia <> 0 and (a.stt_nh = '01' or a.stt_nh = '03') "
           + " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,"
           + " c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
           
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchPhieuXuatTHByKy(int thang, int nam, string nhomPC, string condition)
        {
            string query = string.Format("select ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt," 
            + " sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,gia,gia_bhxh"
            + " from ( "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,"
            + " 0 as sldauky,0 as slnhap,sum(slxuat) as slxuat,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p1_h b on a.ct_id = b.ct_id "
            + " inner join p1_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " group by d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,gia,gia_bhxh "
            + " union all " 
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,"
            + " 0 as sldauky,0 as slnhap,sum(slxuat) as slxuat,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p2_h b on a.ct_id = b.ct_id "
            + " inner join p2_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " group by d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,gia,gia_bhxh "
            + " Union all  "
            + " select  d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngaynhap "
            + " as ngay_tt,0 as sldauky,slnhap,  "
            + " 0 as slxuat,g.gia,g.gia_bhxh from  "
            + " NT_PHIEUNHAP_DTL a  left join dm d on d.ma_hieu = a.ma_hieu   "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam  " + condition
            + " union all   "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx, "
            + " b.dauky as ngay_tt,sldauky,0 as slnhap,   "
            + " 0 as slxuat,gia,gia_bhxh from nt_phieunhap a   "
            + " left join  nt_ky b on a.thang = b.thang and a.nam = b.nam   "
            + " left join dm d on d.ma_hieu = a.ma_hieu   "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam and sldauky <> 0  " + condition
            + " Union all "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngayxuat as ngay_tt,"
            + " 0 as sldauky,0 as slnhap,slxuat,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat_dc a "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " ) as tam "
            + " group by ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt, "
            + " sldauky,gia,gia_bhxh "
            + " order by ma_hieu,ten_hieu,ngay_tt");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchPhieuXuatByKy(int thang, int nam, string nhomPC, string condition)
        {
            string query = string.Format("select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,"
            + " 0 as sldauky,0 as slnhap,slxuat,slxuat as sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p1_h b on a.ct_id = b.ct_id "
            + " inner join p1_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " Union all"
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,"
            + " 0 as sldauky,0 as slnhap, slxuat,slxuat as sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p2_h b on a.ct_id = b.ct_id "
            + " inner join p2_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " Union all "
            + " select  d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngaynhap as ngay_tt,'phiÕu nhËp' as sophieu,0 as sldauky,slnhap,"
            + " 0 as slxuat,0 as sl,0 as gia,0 as gia_bhxh from NT_PHIEUNHAP_DTL a "
            + " left join dm d on d.ma_hieu = a.ma_hieu "
            + " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " union all "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.dauky as ngay_tt,'' as sophieu,sldauky,0 as slnhap, "
            + " 0 as slxuat,0 as sl,0 as gia,0 as gia_bhxh from nt_phieunhap a "
            + " left join  nt_ky b on a.thang = b.thang and a.nam = b.nam "
            + " left join dm d on d.ma_hieu = a.ma_hieu "
            + " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " Union all "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngayxuat as ngay_tt,'XuÊt ®iÒu chØnh' as sophieu,"
            + " 0 as sldauky,0 as slnhap,slxuat,slxuat as sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat_dc a "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam and slxuat <> 0 " + condition
            + " order by d.ma_hieu,ten_hieu,b.ngay_tt,b.sophieu");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int); 
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchPhieuXuatSoSachByKy(int thang, int nam, string nhomPC, string condition)
        {
            string query = string.Format("select ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt,"
            + " sophieu,sum(slxuat) as slxuat,sum(sl) as sl,gia,gia_bhxh from ("
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,slxuat,0 as sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p1_h b on a.ct_id = b.ct_id "
            + " inner join p1_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " Union all"
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,slxuat,0 as sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p2_h b on a.ct_id = b.ct_id "
            + " inner join p2_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " union all "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt, "
            + " b.sophieu,0 as slxuat,sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from " 
            + " p1_h b inner join p1_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu  where  a.stt_nh =  @nhompc " + condition
            + " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + " union all "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt, "
            + " b.sophieu,0 as slxuat,sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "  
            + " p2_h b inner join p2_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu  where  a.stt_nh = @nhompc" + condition
            + " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + " ) as temp "
            + " group by ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt, "
            + " sophieu,gia,gia_bhxh "
            + " order by ma_hieu,ten_hieu,ngay_tt,sophieu");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchByNgay(int thang, int nam, string nhomPC, string tungay, string denngay, string condition)
        {
            string query = string.Format("select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
               + "\r\n" +  " sum(sldauky) as sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,sum(slxuatdc) as slxuatdc,"
               + "\r\n" +  " sum(sldauky)  + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as slcuoiky"
               + "\r\n" +  "   from ("
               + "\r\n" +  "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
               + "\r\n" +  "   sum(sldauky)  + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as sldauky,0 as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky "
               + "\r\n" +  "   from ("
               + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
               + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" +  "   isnull(c.sldauky,0)as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" +  "   left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = @thang and c.nam = @nam "
               + "\r\n" +  "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" +  "   and ngaynhap < @tungay "
               + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, c.sldauky,a.duongdung,a.lieudung"
               + "\r\n" +  "   Union all"
               + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
               + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" +  "   0 as sldauky,0 as slnhap,isnull(sum(slxuat),0) as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" +  "   left join NT_PHIEUXUAT g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" +  "   and ngay_xuat < @tungay "
               + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung"
               + "\r\n" +  "   Union all"
               + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
               + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" +  "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" +  "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" +  "   and ngayxuat < @tungay "
               + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
               + "\r\n" +  "   ) as tam "
               + "\r\n" +  "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
               + "\r\n" +  "   union all "
               + "\r\n" +  "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
               + "\r\n" + "    0 as sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,sum(slxuatdc) as slxuatdc,0 as slcuoiky "
               + "\r\n" +  "   from ( "
               + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
               + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" +  "   0 as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" +  "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" +  "   and ngaynhap >= @tungay and ngaynhap <= @denngay"
               + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, a.duongdung,a.lieudung "
               + "\r\n" +  "   Union all "
               + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
               + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,  "
               + "\r\n" +  "   0 as sldauky,0 as slnhap,isnull(sum(slxuat),0) as slxuat,0 as slxuatdc,0 as slcuoiky from dm a  "
               + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1  "
               + "\r\n" +  "   left join NT_PHIEUXUAT g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam  "
               + "\r\n" +  "   and ngay_xuat >= @tungay and ngay_xuat <= @denngay "
               + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh,  "
               + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
               + "\r\n" +  "   Union all "
               + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
               + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,  "
               + "\r\n" +  "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc,0 as slcuoiky from dm a  "
               + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1  "
               + "\r\n" +  "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam  "
               + "\r\n" +  "   and ngayxuat >= @tungay and ngayxuat <= @denngay  "
               + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh,  "
               + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
               + "\r\n" +  "   ) as tam "
               + "\r\n" +  "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
               + "\r\n" +  "   ) as tam1  "
               + "\r\n" +  "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh");
            

            

            SqlParameter[] sqlParameters = new SqlParameter[5];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            sqlParameters[3] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[3].Value = tungay;
            sqlParameters[4] = new SqlParameter("@denngay", SqlDbType.NVarChar);
            sqlParameters[4].Value = denngay;
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchByKy(int thang,int nam,string nhomPC,string condition)
        {
            string query = string.Format("select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,"
            + " a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,"
            + " isnull(c.sldauky,0) as sldauky,isnull(c.slnhap,0) as slnhap,isnull(sum(slxuat),0) as slxuat,isnull(slxuatdc,0) as slxuatdc,"
            + " isnull(c.sldauky,0) + isnull(c.slnhap,0) - isnull(sum(slxuat),0) - isnull(slxuatdc,0) as slcuoiky"
            + " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1"
            + " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu"
            + " and c.thang = @thang and c.nam = @nam"
            + " left join NT_PHIEUXUAT d on a.stt_nh = d.stt_nh and a.ma_hieu = d.ma_hieu"
            + " and d.thang = @thang and d.nam = @nam "
            + " where  a.stt_nh = @nhompc  and a.chon = 1 and b.gia <> 0 " + condition 
            + " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,"
            + " c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung");
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
           
            return conn.executeSelectQuery(query, sqlParameters);
         }
        public DataTable SearchNgayNhap(int thang, int nam, string mahieu)
        {
            string query = string.Format("select ngaynhap,slnhap from NT_PHIEUNHAP_DTL "
            + " WHERE ma_hieu = @mahieu and thang = @thang and nam = @nam and slnhap <> 0    ");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(mahieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchNgayXuat(int thang, int nam, string mahieu)
        {
            string query = string.Format("select ngayxuat,slxuat from NT_PHIEUXUAT_DC "
            + " WHERE ma_hieu = @mahieu and thang = @thang and nam = @nam and slxuat <> 0    ");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(mahieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchSLXuat(int thang, int nam, string mahieu)
        {
            string query = string.Format("select ngay_xuat,SUM(slxuat) as slxuat from NT_PHIEUXUAT "
            + " WHERE ma_hieu = @mahieu and thang = @thang and nam = @nam group by ngay_xuat ");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(mahieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public bool InsertRecorDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Insert into NT_PHIEUNHAP_DTL(stt_nh,ma_hieu,thang,nam,ngaynhap,slnhap) "
            + " VALUES (@sttnh,@mahieu,@thang,@nam,@ngaynhap,@slnhap)");
            SqlParameter[] sqlParameters = new SqlParameter[6];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            sqlParameters[4] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
            sqlParameters[4].Value = phieunhapVO.Ngaynhap;

            sqlParameters[5] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Sl;

           return  conn.executeInsertQuery(query, sqlParameters);
        }
        public bool InsertRecorDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Insert into NT_PHIEUXUAT_DC(stt_nh,ma_hieu,thang,nam,Ngayxuat,slxuat) "
            + " VALUES (@sttnh,@mahieu,@thang,@nam,@ngayxuat,@slxuat)");
            SqlParameter[] sqlParameters = new SqlParameter[6];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            sqlParameters[4] = new SqlParameter("@ngayxuat", SqlDbType.DateTime);
            sqlParameters[4].Value = phieunhapVO.Ngayxuat;

            sqlParameters[5] = new SqlParameter("@slxuat", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Slx;

            return conn.executeInsertQuery(query, sqlParameters);
        }
        public PhieunhapVO InsertRecord(PhieunhapVO phieunhapVO)
        {

            string query = string.Format("Insert into NT_PHIEUNHAP(stt_nh,ma_hieu,thang,nam,sldauky,slnhap,slxuatdc) "
            + " VALUES (@sttnh,@mahieu,@thang,@nam,@sldauky,@slnhap,@slxuatdc)");
            SqlParameter[] sqlParameters = new SqlParameter[7];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            sqlParameters[4] = new SqlParameter("@sldauky", SqlDbType.Decimal);
            sqlParameters[4].Value = phieunhapVO.Sldauky;

            sqlParameters[5] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Slnhap;

            sqlParameters[6] = new SqlParameter("@slxuatdc", SqlDbType.Decimal);
            sqlParameters[6].Value = phieunhapVO.Slxuatdc;

                  
            conn.executeInsertQuery(query, sqlParameters);
            //insert bang chi tiet
            InsertRecorDTL(phieunhapVO);

            return phieunhapVO;

        }
        public bool UpdateDM(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("UPDATE DM SET "
            + "lieudung = @nhomthuoc,duongdung = @bietduoc "
            + "where  stt_nh = @sttnh and ma_hieu = @mahieu ");

           
            SqlParameter[] sqlParameters = new SqlParameter[4];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@nhomthuoc", SqlDbType.NVarChar);
            sqlParameters[2].Value = phieunhapVO.Nhomthuoc;

            sqlParameters[3] = new SqlParameter("@bietduoc", SqlDbType.NVarChar);
            sqlParameters[3].Value = phieunhapVO.Bietduoc;


            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool UpdateRecordDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("UPDATE NT_PHIEUNHAP_DTL SET "
            + "slnhap = slnhap + @slnhap "
            + "where  ma_hieu = @mahieu and ngaynhap= @ngaynhap ");

            SqlParameter[] sqlParameters = new SqlParameter[3];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngaynhap;

            sqlParameters[2] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[2].Value = phieunhapVO.Sl;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool UpdateRecordDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("UPDATE NT_PHIEUXUAT_DC SET "
            + "slxuat = slxuat + @slxuat "
            + "where  ma_hieu = @mahieu and ngayxuat= @ngayxuat ");

            SqlParameter[] sqlParameters = new SqlParameter[3];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngayxuat", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngayxuat;

            sqlParameters[2] = new SqlParameter("@slxuat", SqlDbType.Decimal);
            sqlParameters[2].Value = phieunhapVO.Slx;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool UpdateRecord(PhieunhapVO phieunhapVO)
        {

            string query = string.Format("UPDATE NT_PHIEUNHAP SET "
            + "slnhap = @slnhap,slxuatdc = @slxuatdc "
            + "where  stt_nh = @sttnh and ma_hieu = @mahieu and thang= @thang and nam = @nam ");
            
            SqlParameter[] sqlParameters = new SqlParameter[6];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;
                       
            sqlParameters[4] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[4].Value = phieunhapVO.Slnhap;

            sqlParameters[5] = new SqlParameter("@slxuatdc", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Slxuatdc;
            if (phieunhapVO.Sl != 0)
            {
                if (CheckPhieuNhapDTL(phieunhapVO).Rows.Count == 0)
                {
                    InsertRecorDTL(phieunhapVO);
                }
                else 
                {
                    UpdateRecordDTL(phieunhapVO);
                }
            }
            if (phieunhapVO.Slx != 0)
            {
                if (CheckPhieuXuatDC(phieunhapVO).Rows.Count == 0)
                {
                    InsertRecorDC(phieunhapVO);
                }
                else
                {
                    UpdateRecordDC(phieunhapVO);
                }
            }           
            conn.executeInsertQuery(query, sqlParameters);
            return true ;

        }
        public bool DeleteRecordDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Delete NT_PHIEUNHAP_DTL"
            + "where  ma_hieu = @mahieu and ngaynhap= @ngaynhap ");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngaynhap;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool DeleteRecordDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Delete NT_PHIEUXUAT_DC"
            + "where  ma_hieu = @mahieu and ngayxuat= @ngayxuat ");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngayxuat", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngayxuat;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public DataTable SlXuat(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select isnull(sum(slxuat),0) as slxuat from dbo.NT_phieuxuat where "
            + " stt_nh = @sttnh and ma_hieu = @mahieu and thang= @thang and nam = @nam");

            SqlParameter[] sqlParameters = new SqlParameter[4];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuNhapDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select * from dbo.NT_PHIEUNHAP_DTL where "
            + " ma_hieu = @mahieu and ngaynhap = @ngaynhap");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu  ;

            sqlParameters[1] = new SqlParameter("@ngaynhap", SqlDbType.SmallDateTime);
            sqlParameters[1].Value = phieunhapVO.Ngaynhap ;
                        
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuXuatDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select * from dbo.NT_PHIEUXUAT_DC where "
            + " ma_hieu = @mahieu and ngayxuat = @ngayxuat");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngayxuat", SqlDbType.SmallDateTime);
            sqlParameters[1].Value = phieunhapVO.Ngayxuat;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuXuat(int thang,int nam)
        {

            string query = string.Format("select * from dbo.NT_PhieuXuat where "
            + " thang= @thang and nam = @nam");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuNhap(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select * from dbo.NT_PHIEUNHAP where "
            + " stt_nh = @sttnh and ma_hieu = @mahieu and thang= @thang and nam = @nam");

            SqlParameter[] sqlParameters = new SqlParameter[4];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public bool Chuyenky(int thang,int nam,string tungay)
        {
            int thangmoi = thang + 1;
            int nammoi = nam;
            if (thangmoi == 13)
            {
                thangmoi = 1;
                nammoi = nammoi + 1;
            }

            //string query = string.Format("insert into NT_PHIEUNHAP"
            //+ " select a.stt_nh, a.ma_hieu,@thangmoi,@nammoi,"
            //+ " isnull(c.sldauky,0) + isnull(c.slnhap,0) - isnull(sum(slxuat),0) - isnull(slxuatdc,0),0,0"
            //+ " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu  "
            //+ " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu "
            //+ " and c.thang = @thang and c.nam = @nam left join NT_PHIEUXUAT d "
            //+ " on a.stt_nh = d.stt_nh and a.ma_hieu = d.ma_hieu and d.thang = @thang and "
            //+ " d.nam = @nam  "
            //+ " where  a.stt_nh in ('01','03')  and a.chon = 1 and gia <> 0  "
            //+ " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,slxuatdc, "
            //+ " c.sldauky,c.slnhap");

           string query = string.Format("insert into NT_PHIEUNHAP"
               + "\r\n" + "  select stt_nh, ma_hieu,@thangmoi,@nammoi, "
               + "\r\n" + " sum(sldauky),sum(slnhap), sum(slxuatdc)"
               + "\r\n" + "   from ("
               + "\r\n" + "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
               + "\r\n" + "   sum(sldauky)  + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as sldauky,0 as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky "
               + "\r\n" + "   from ("
               + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
               + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" + "   isnull(c.sldauky,0)as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" + "   left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = @thang and c.nam = @nam "
               + "\r\n" + "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" + "   and ngaynhap < @tungay "
               + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, c.sldauky,a.duongdung,a.lieudung"
               + "\r\n" + "   Union all"
               + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
               + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" + "   0 as sldauky,0 as slnhap,isnull(sum(slxuat),0) as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" + "   left join NT_PHIEUXUAT g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" + "   and ngay_xuat < @tungay "
               + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung"
               + "\r\n" + "   Union all"
               + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
               + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" + "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" + "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" + "   and ngayxuat < @tungay "
               + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
               + "\r\n" + "   ) as tam "
               + "\r\n" + "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
               + "\r\n" + "   union all "
               + "\r\n" + "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
               + "\r\n" + "    0 as sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,sum(slxuatdc) as slxuatdc,0 as slcuoiky "
               + "\r\n" + "   from ( "
               + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
               + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
               + "\r\n" + "   0 as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
               + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
               + "\r\n" + "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
               + "\r\n" + "   and ngaynhap >= @tungay "
               + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
               + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, a.duongdung,a.lieudung "
               + "\r\n" + "   Union all "
               + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
               + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,  "
               + "\r\n" + "   0 as sldauky,0 as slnhap,isnull(sum(slxuat),0) as slxuat,0 as slxuatdc,0 as slcuoiky from dm a  "
               + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1  "
               + "\r\n" + "   left join NT_PHIEUXUAT g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam  "
               + "\r\n" + "   and ngay_xuat >= @tungay "
               + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh,  "
               + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
               + "\r\n" + "   Union all "
               + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
               + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,  "
               + "\r\n" + "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc,0 as slcuoiky from dm a  "
               + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1  "
               + "\r\n" + "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam  "
               + "\r\n" + "   and ngayxuat >= @tungay "
               + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh,  "
               + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
               + "\r\n" + "   ) as tam "
               + "\r\n" + "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
               + "\r\n" + "   ) as tam1  "
               + "\r\n" + "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh");

            SqlParameter[] sqlParameters = new SqlParameter[5];

            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value =thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;

            sqlParameters[2] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[2].Value = thangmoi;

            sqlParameters[3] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[3].Value = nammoi;

            sqlParameters[4] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[4].Value = tungay;
                        
            conn.executeInsertQuery(query, sqlParameters);

            /*up date phieu nhap*/
            query = string.Format("UPDATE NT_PHIEUNHAP_DTL SET THANG = @thangmoi,nam = @nammoi "
                + " where ngaynhap >= @tungay ");

            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);

            /*up date phieu xuat*/
            query = string.Format("UPDATE NT_PHIEUXUAT SET THANG = @thangmoi,nam = @nammoi "
                + " where ngay_xuat >= @tungay ");

            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);
            /*up date phieu dieu chinh*/

            query = string.Format("UPDATE NT_PHIEUXUAT_DC SET THANG = @thangmoi,nam = @nammoi "
               + " where ngayxuat >= @tungay ");

            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);

            /*Insert Ky*/
            query = string.Format("insert into nt_ky(thang,nam,ky,dauky) "
            + " values(@thangmoi,@nammoi,1,@tungay)");
            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("Update NT_KY set ky = 0 where thang = @thang and nam = @nam");
            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;
            conn.executeInsertQuery(query, sqlParameters);


            return true;
        }
        public bool Luiky(int thang, int nam)
        {
            int thangmoi = thang - 1;
            int nammoi = nam;
            if (thangmoi == 0)
            {
                thangmoi = 12;
                nammoi = nammoi - 1;
            }
            string query = string.Format("delete NT_PHIEUNHAP where thang = @thang and nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;
            conn.executeInsertQuery(query, sqlParameters);

            /*up date phieu nhap*/
            query = string.Format("UPDATE NT_PHIEUNHAP_DTL SET THANG = @thangmoi,nam = @nammoi "
                + " where ngaynhap >= (select dauky from nt_ky where ky = 1) and thang = @thang and nam = @nam ");

            sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = nam;
          
            conn.executeInsertQuery(query, sqlParameters);

            /*up date phieu xuat*/
            query = string.Format("UPDATE NT_PHIEUXUAT SET THANG = @thangmoi,nam = @nammoi "
                + " where ngay_xuat >= (select dauky from nt_ky where ky = 1) and thang = @thang and nam = @nam ");

            sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = nam;

            conn.executeInsertQuery(query, sqlParameters);
            /*up date phieu dieu chinh*/

            query = string.Format("UPDATE NT_PHIEUXUAT_DC SET THANG = @thangmoi,nam = @nammoi "
               + " where ngayxuat >= (select dauky from nt_ky where ky = 1) and thang = @thang and nam = @nam ");

            sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = nam;
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("delete NT_KY where ky = 1");
            sqlParameters = new SqlParameter[0];
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("Update NT_KY set ky = 1  where thang = @thangmoi and nam = @nammoi");
            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;
            conn.executeInsertQuery(query, sqlParameters);



            return true;
        }

        public DataTable kiemtraDAO(string ngay)
        {
            string query = string.Format("select ho_ten,mathe,ngay_tt,sophieu,tongtien - tienct as sosanh,tongtien,tienct,'ngoaitru' as loai from "
               + " ( "
               + "  select ho_ten,mathe,ngay_tt,sophieu,sum(tienct) as tienct,sum(tongtien) as tongtien from "
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,tong_tien as tongtien,0 as tienct from p1_du a inner join p1_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03') "
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,0 as tongtien,sum(gia*sl) as tienct from p1_d a inner join p1_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03')"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam1 where tongtien - tienct <> 0"
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,tongtien - tienct as sosanh,tongtien,tienct,'noitru' as loai from"
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,sum(tienct) as tienct,sum(tongtien) as tongtien from"
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,tong_tien as tongtien,0 as tienct from p2_du a inner join p2_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03')  "
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,0 as tongtien,sum(gia*sl) as tienct from p2_d a inner join p2_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03')"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam1 where tongtien - tienct <> 0"
               + "  order by ngay_tt desc");
            
            SqlParameter[] sqlParameters = new SqlParameter[1];

            sqlParameters[0] = new SqlParameter("@ngay", SqlDbType.NVarChar);
            sqlParameters[0].Value = ngay;

            return conn.executeSelectQuery(query, sqlParameters);
        }

    }
    
   
}
